package org.compiere.model;

import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Properties;
import java.util.logging.Level;

import org.compiere.util.CLogger;
import org.compiere.util.DB;
import org.compiere.util.Env;

public class Mhrmtimesheet extends X_hrm_timesheet {
	
	/**
	 * 
	 */
	private static final long serialVersionUID = -600144246910177160L;
	
	/**	Static Logger	*/
	private static CLogger	s_log	= CLogger.getCLogger (Mhrmtimesheet.class);

	public Mhrmtimesheet(Properties ctx, int hrm_timesheet_ID, String trxName) {
		super(ctx, hrm_timesheet_ID, trxName);
	}

	public Mhrmtimesheet(Properties ctx, ResultSet rs, String trxName) {
		super(ctx, rs, trxName);
	}
	
	/**
	 * Search for entries in the time sheet
	 * 
	 * @param C_BPartner_ID
	 * @param dt
	 * @return  Model time sheet
	 */
	public static Mhrmtimesheet getTimeSheet(int C_BPartner_ID, Timestamp dt) {
	
		//
	    PreparedStatement pstmt = null;
		ResultSet rs = null;
		Mhrmtimesheet timeSheet = null;
			
		// Employee data from the timesheets
		String sql_ = "SELECT * FROM HRM_TIMESHEET " +
						"WHERE ISACTIVE='Y' " +
						"AND AD_CLIENT_ID=" + Env.getAD_Client_ID(Env.getCtx()) + " \n" +
						"AND C_BPARTNER_ID=" + C_BPartner_ID + " \n" +
						"AND DATEFROM=" + DB.TO_DATE(dt, true) + " ";
			try
			{
				pstmt = DB.prepareStatement(sql_,null);
				rs = pstmt.executeQuery();	
				if(rs.next())
					timeSheet = new Mhrmtimesheet(Env.getCtx(), rs, null);
			}
			catch (SQLException e)
			{
				s_log.log(Level.SEVERE, "product", e);
			}
			finally
			{
				DB.close(rs, pstmt);
				rs = null; pstmt = null;
			}	
		return timeSheet;
		
	}
	
public static int getHourquota(int C_BPartner_ID, Timestamp dt){
		
		if(C_BPartner_ID == 0)
    		return 0;
				
		int result = 0;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		
		// 
		String sql_ = "SELECT SUM(HRM_HOURQUOTA) FROM HRM_TIMESHEET \n " +
						" WHERE ISACTIVE='Y' AND HRM_HOURQUOTA !=0 " +
						" AND AD_CLIENT_ID=" + Env.getAD_Client_ID(Env.getCtx()) + " \n" +
						" AND C_BPARTNER_ID=" + C_BPartner_ID + " \n" +
						" AND " + DB.TO_DATE(dt) + " BETWEEN date_trunc('MONTH'::text, DATEFROM) " +
						" AND (date_trunc('MONTH'::text, DATEFROM) + '1 mon'::interval - '1 day'::interval)";
		try
		{
			pstmt = DB.prepareStatement(sql_,null);				
			rs = pstmt.executeQuery();
			if(rs.next())
				return rs.getInt(1);		
		}
		catch (SQLException e)
		{
			s_log.log(Level.SEVERE, "product", e);
		}
		finally
		{
			DB.close(rs, pstmt);
			rs = null; pstmt = null;
		}

		return result;
	}

public static Mhrmtimesheet[] getTimesheetArray(int C_BPartner_ID, Timestamp sdt, Timestamp edt) {
	
	//
    PreparedStatement pstmt = null;
	ResultSet rs = null;
	Mhrmtimesheet hrmts = null;
		
	ArrayList<Mhrmtimesheet> list = new ArrayList<Mhrmtimesheet>();
	// 
		String sql_ = "SELECT *	FROM HRM_TIMESHEET " +
						"WHERE ISACTIVE = 'Y' " +
							"AND AD_CLIENT_ID =" + Env.getAD_Client_ID(Env.getCtx()) + " \n" +
							"AND C_BPARTNER_ID = " + C_BPartner_ID + " \n" +
							"AND DATEFROM >= " + DB.TO_DATE(sdt, true) + " \n" +
							"AND DATEFROM <= " + DB.TO_DATE(edt, true) + " ";
		try
		{
			pstmt = DB.prepareStatement(sql_,null);			
			rs = pstmt.executeQuery();	
			while(rs.next()){
				hrmts = new  Mhrmtimesheet(Env.getCtx(), rs, null);
				list.add(hrmts);
			}
		}
		catch (SQLException e)
		{
			s_log.log(Level.SEVERE, "product", e);
		}
		finally
		{
			DB.close(rs, pstmt);
			rs = null; pstmt = null;
		}	
		
		return list.toArray(new Mhrmtimesheet[list.size()]);
		}

/**
 *  Define how calendar days employee is working in first month
 * @param C_BPartner_ID
 * @param dt
 * @return
 */
public static int getHiringDay(int C_BPartner_ID, Timestamp dt){
	
	if(C_BPartner_ID == 0)
		return 0;
			
	int result = 0;
	PreparedStatement pstmt = null;
	ResultSet rs = null;
	
	// 
	String sql_ = "SELECT count(*) FROM HRM_TIMESHEET \n " +
					" WHERE C_BPARTNER_ID=" + C_BPartner_ID + " \n " +
					" AND DATEFROM BETWEEN " + DB.TO_DATE(dt) + " AND DATE_TRUNC('MONTH'::TEXT, " + DB.TO_DATE(dt) + ") + '1 MON'::interval - '1 day'::interval";
	try
	{
		pstmt = DB.prepareStatement(sql_,null);				
		rs = pstmt.executeQuery();
		if(rs.next())
			return rs.getInt(1);		
	}
	catch (SQLException e)
	{
		s_log.log(Level.SEVERE, "product", e);
	}
	finally
	{
		DB.close(rs, pstmt);
		rs = null; pstmt = null;
	}

	return result;
	}

/**
 *  Define how calendar days employee is working to the job before was dismissal
 * @param C_BPartner_ID
 * @param dt
 * @return
 */
public static int getDismissalDay(int C_BPartner_ID, Timestamp dt){
	
	if(C_BPartner_ID == 0)
		return 0;
			
	int result = 0;
	PreparedStatement pstmt = null;
	ResultSet rs = null;
	
	// 
	String sql_ = "SELECT count(*) FROM HRM_TIMESHEET \n " +
					" WHERE C_BPARTNER_ID=" + C_BPartner_ID + " \n " +
					" AND DATEFROM BETWEEN DATE_TRUNC('MONTH'::TEXT, " + DB.TO_DATE(dt) + ") AND " + DB.TO_DATE(dt);
	try
	{
		pstmt = DB.prepareStatement(sql_,null);				
		rs = pstmt.executeQuery();
		if(rs.next())
			return rs.getInt(1);		
	}
	catch (SQLException e)
	{
		s_log.log(Level.SEVERE, "product", e);
	}
	finally
	{
		DB.close(rs, pstmt);
		rs = null; pstmt = null;
	}

	return result;
	}
}
